unit uFiltro_Imo;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Grids, DBGrids, JvExDBGrids, JvDBGrid, JvExStdCtrls, JvEdit,
  JvValidateEdit, RXCtrls, StdCtrls, Mask, JvExMask, JvToolEdit,
  JvMaskEdit, JvCheckedMaskEdit, JvDatePickerEdit, Buttons, ExtCtrls,
  MMJPanel, FMTBcd, SqlExpr, Provider, DB, DBClient;

type
  TfFiltro_Imo = class(TForm)
    MMJPanel1: TMMJPanel;
    btn1: TSpeedButton;
    btn2: TSpeedButton;
    btn3: TSpeedButton;
    btn4: TSpeedButton;
    btn5: TSpeedButton;
    btn6: TSpeedButton;
    btn7: TSpeedButton;
    Data2: TJvDatePickerEdit;
    MMJPanel3: TMMJPanel;
    lbl1: TRxLabel;
    vDesp: TJvValidateEdit;
    vRec: TJvValidateEdit;
    vTot: TJvValidateEdit;
    D_Contas: TDataSource;
    C_Contas: TClientDataSet;
    P_Contas: TDataSetProvider;
    S_Contas: TSQLDataSet;
    S_ContasCOD_LANCAMENTO: TIntegerField;
    S_ContasCOD_CONTAS: TIntegerField;
    S_ContasVENCIMENTO: TDateField;
    S_ContasVALOR_TITULO: TFloatField;
    S_ContasSTATUS: TStringField;
    S_ContasVALOR: TFloatField;
    S_ContasPARCELA: TIntegerField;
    S_ContasCOD_MOVIMENTO: TIntegerField;
    S_ContasVIA: TStringField;
    S_ContasGRUPO: TStringField;
    S_ContasTIPOCONTA: TStringField;
    S_ContasDATAPAGTO: TDateField;
    S_ContasSELECIONOU: TStringField;
    S_ContasVALORPAGO: TFloatField;
    S_ContasVALORTROCO: TFloatField;
    S_ContasVALORJUROS: TFloatField;
    S_ContasVALORDESCONTO: TFloatField;
    S_ContasHISTORICO: TStringField;
    S_ContasCOD_FORNECEDOR: TIntegerField;
    S_ContasCOD_CLIENTE: TIntegerField;
    S_ContasCOD_CAIXA: TIntegerField;
    S_ContasCOD_PGTO: TIntegerField;
    C_ContasCOD_LANCAMENTO: TIntegerField;
    C_ContasCOD_CONTAS: TIntegerField;
    C_ContasVENCIMENTO: TDateField;
    C_ContasVALOR_TITULO: TFloatField;
    C_ContasSTATUS: TStringField;
    C_ContasVALOR: TFloatField;
    C_ContasPARCELA: TIntegerField;
    C_ContasCOD_MOVIMENTO: TIntegerField;
    C_ContasVIA: TStringField;
    C_ContasGRUPO: TStringField;
    C_ContasTIPOCONTA: TStringField;
    C_ContasDATAPAGTO: TDateField;
    C_ContasSELECIONOU: TStringField;
    C_ContasVALORPAGO: TFloatField;
    C_ContasVALORTROCO: TFloatField;
    C_ContasVALORJUROS: TFloatField;
    C_ContasVALORDESCONTO: TFloatField;
    C_ContasHISTORICO: TStringField;
    C_ContasCOD_FORNECEDOR: TIntegerField;
    C_ContasCOD_CLIENTE: TIntegerField;
    C_ContasCOD_CAIXA: TIntegerField;
    C_ContasCOD_PGTO: TIntegerField;
    S_ContasFORNECEDOR: TStringField;
    S_ContasCLIENTE: TStringField;
    C_ContasFORNECEDOR: TStringField;
    C_ContasCLIENTE: TStringField;
    S_ContasDESC_CAIXA: TStringField;
    S_ContasDESC_FORMAPGTO: TStringField;
    C_ContasDESC_CAIXA: TStringField;
    C_ContasDESC_FORMAPGTO: TStringField;
    JvDBGrid1: TJvDBGrid;
    sTotais: TSQLDataSet;
    sTotaisTOTPENDENTE: TFloatField;
    sTotaisTOTPAGO: TFloatField;
    sTotaisTOTAL: TFloatField;
    procedure btn3Click(Sender: TObject);
    procedure btn7Click(Sender: TObject);
    procedure btn2Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure btn6Click(Sender: TObject);
    procedure JvDBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    { Private declarations }
    procedure mostracontas; 
  public
    { Public declarations }
  end;

var
  fFiltro_Imo: TfFiltro_Imo;

implementation

uses uGeraLancImo, UDM, U_FiltroContasImob, sCtrlResize, U_BAIXACONTAS,
  U_LANCREC;

{$R *.dfm}

procedure TfFiltro_Imo.btn3Click(Sender: TObject);
begin
   if (dm.vTipo = 'Despesa') then
     fGeraLancImo.ShowModal
   else
     F_LANCREC.ShowModal;
end;

procedure TfFiltro_Imo.btn7Click(Sender: TObject);
begin
  Close;
end;

procedure TfFiltro_Imo.mostracontas;
var V_TEXTO, V_SQL, V_SQL1 :String;
begin
   V_TEXTO := 'SELECT a.COD_LANCAMENTO, a.COD_CONTAS, a.VENCIMENTO, a.VALOR_TITULO, ' +
            'a.STATUS, a.VALOR, a.PARCELA,  a.COD_MOVIMENTO, a.VIA, a.GRUPO, a.TIPOCONTA, ' +
            'a.DATAPAGTO, a.SELECIONOU, a.VALORPAGO, a.VALORTROCO, a.VALORJUROS, ' +
            'a.VALORDESCONTO, a.HISTORICO, a.COD_FORNECEDOR, a.COD_CLIENTE, a.COD_CAIXA, a.COD_PGTO, ' +
            'f.NOME as FORNECEDOR, c.NOME as CLIENTE, cx.DESC_CAIXA, fp.DESC_FORMAPGTO ' +
            'FROM LANCAMENTOS a ' +
            'left outer join FUNCIONARIOS f on f.COD_FUNCIONARIO = a.COD_FORNECEDOR ' +
            'left outer join CLIENTE c on c.COD_CLIENTE = a.COD_CLIENTE ' +
            'left outer join CAIXA cx on cx.ID_CAIXA = a.COD_CAIXA ' +
            'left outer join FORMA_PGTO fp on fp.ID_FORMAPGTO = a.COD_PGTO ' +
            ' where a.GRUPO = ' + QuotedStr('Imobiliaria') +
            ' and a.TIPOCONTA = ' + QuotedStr(dm.vTipo);
   if (dm.vTipo = 'Despesa') then
   begin
     if (DM.TextoForCli <> '') then
       V_SQL := V_SQL + ' and f.NOME like ' + QuotedStr(DM.TextoForCli);
   end;
   if (dm.vTipo = 'Receita') then
   begin
     if (DM.TextoForCli <> '') then
       V_SQL := V_SQL + ' and c.NOME like ' + QuotedStr(DM.TextoForCli);
   end;
   if (DM.vDtaIni > 0) then
     if (DM.vDtaFim > 0) then
     begin
       V_SQL := V_SQL + ' and a.VENCIMENTO between ' +
       QuotedStr(FormatDateTime('MM/dd/yyyy', DM.vDtaIni)) +
       ' and ' + QuotedStr(FormatDateTime('MM/dd/yyyy', DM.vDtaFim));
     end;
   if (DM.vDtaPini > 0) then
     if (DM.vDtaPfim > 0) then
     begin
       V_SQL := V_SQL + ' and a.DATAPAGTO between ' +
       QuotedStr(FormatDateTime('MM/dd/yyyy', DM.vDtaPini)) +
       ' and ' + QuotedStr(FormatDateTime('MM/dd/yyyy', DM.vDtaPfim));
     end;
   if (DM.TextoStatus <> '') then
     V_SQL := V_SQL + ' and a.STATUS = ' + QuotedStr(DM.TextoStatus);

   if (DM.TextoCaixa <> '') then
       V_SQL := V_SQL + ' and cx.DESC_CAIXA like ' + QuotedStr(DM.TextoCaixa);

   if (DM.TextoFormapgto <> '') then
       V_SQL := V_SQL + ' and fp.DESC_FORMAPGTO like ' + QuotedStr(DM.TextoFormapgto);

   if (dm.vTipo = 'Despesa') then
     V_SQL1 := ' order by a.COD_FORNECEDOR, a.VENCIMENTO '
   else
     V_SQL1 := ' order by a.COD_CLIENTE, a.VENCIMENTO ';

   if (C_Contas.Active) then
      C_Contas.Close;
   C_Contas.CommandText := V_TEXTO +  V_SQL + V_SQL1;
   C_Contas.Open;
   // Totaliza Pendentes
   V_TEXTO := 'SELECT SUM(a.VALOR) as TOTPENDENTE, SUM(a.VALORPAGO) as TOTPAGO, ' +
              'SUM(a.VALOR + a.VALORPAGO) as TOTAL ' +
              'FROM LANCAMENTOS a ' +
              'left outer join FUNCIONARIOS f on f.COD_FUNCIONARIO = a.COD_FORNECEDOR ' +
              'left outer join CLIENTE c on c.COD_CLIENTE = a.COD_CLIENTE ' +
              'left outer join CAIXA cx on cx.ID_CAIXA = a.COD_CAIXA ' +
              'left outer join FORMA_PGTO fp on fp.ID_FORMAPGTO = a.COD_PGTO ' +
              'where a.GRUPO = ' + QuotedStr('Imobiliaria') +
              ' and a.STATUS = ' + QuotedStr('Pendente') +
              ' and a.TIPOCONTA = ' + QuotedStr(dm.vTipo);
   if (sTotais.Active) then
     sTotais.Close;
   sTotais.CommandText := V_TEXTO + V_SQL;
   sTotais.Open;
   vDesp.Value := sTotaisTOTPENDENTE.Value;

   V_TEXTO := 'SELECT SUM(a.VALOR) as TOTPENDENTE, SUM(a.VALORPAGO) as TOTPAGO, ' +
              'SUM(a.VALOR + a.VALORPAGO) as TOTAL ' +
              'FROM LANCAMENTOS a ' +
              'left outer join FUNCIONARIOS f on f.COD_FUNCIONARIO = a.COD_FORNECEDOR ' +
              'left outer join CLIENTE c on c.COD_CLIENTE = a.COD_CLIENTE ' +
              'left outer join CAIXA cx on cx.ID_CAIXA = a.COD_CAIXA ' +
              'left outer join FORMA_PGTO fp on fp.ID_FORMAPGTO = a.COD_PGTO ' +
              'where a.GRUPO = ' + QuotedStr('Imobiliaria') +
              ' and a.STATUS = ' + QuotedStr('Pago') +
              ' and a.TIPOCONTA = ' + QuotedStr(dm.vTipo);
   if (sTotais.Active) then
     sTotais.Close;
   sTotais.CommandText := V_TEXTO + V_SQL;
   sTotais.Open;
   vRec.Value := sTotaisTOTPAGO.Value;
   vTot.Value := vDesp.Value + vRec.Value;
end;

procedure TfFiltro_Imo.btn2Click(Sender: TObject);
begin
  dm.TextoForCli := '';
  dm.TextoStatus := '';
  dm.TextoCaixa := '';
  dm.TextoFormapgto := '';
  dm.vDtaIni := 0;
  dm.vDtaFim := 0;
  dm.vDtaPini := 0;
  dm.vDtaPfim := 0;
  F_FiltroContasImob.ShowModal;
  mostracontas;
end;

procedure TfFiltro_Imo.FormShow(Sender: TObject);
begin
   sCtrlResize.CtrlResize(TForm(fFiltro_Imo));
   DM.vDtaIni := Now;
   DM.vDtaFim := Now;
   mostracontas;
end;

procedure TfFiltro_Imo.btn6Click(Sender: TObject);
begin
   DM.vCodLancamento := C_ContasCOD_LANCAMENTO.AsInteger;
   F_BAIXACONTAS.ShowModal;
end;

procedure TfFiltro_Imo.JvDBGrid1DrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumn;
  State: TGridDrawState);
begin
  if Column.Field = C_ContasSTATUS then
   if C_ContasSTATUS.AsString = 'Pago' then
   begin
     JvDBGrid1.Canvas.Font.Color := clBlue;
     JvDBGrid1.DefaultDrawDataCell(Rect, Column.Field,State);
   end
   else
   begin
     JvDBGrid1.Canvas.Font.Color := clRed;
     JvDBGrid1.DefaultDrawDataCell(Rect, Column.Field,State);
   end;

  if Column.Field = C_ContasVALORPAGO then
   if C_ContasSTATUS.AsString = 'Pago' then
   begin
     JvDBGrid1.Canvas.Font.Color := clBlue;
     JvDBGrid1.DefaultDrawDataCell(Rect, Column.Field,State);
   end;

  if Column.Field = C_ContasVALOR then
   if C_ContasSTATUS.AsString = 'Pendente' then
   begin
     JvDBGrid1.Canvas.Font.Color := clRed;
     JvDBGrid1.DefaultDrawDataCell(Rect, Column.Field,State);
   end;




end;

procedure TfFiltro_Imo.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  dm.TextoForCli := '';
  dm.TextoStatus := '';
  dm.TextoCaixa := '';
  dm.TextoFormapgto := '';
  dm.vDtaIni := 0;
  dm.vDtaFim := 0;
  dm.vDtaPini := 0;
  dm.vDtaPfim := 0;
end;

end.
